AddNew, Update, CancelUpdate Method Example

The following example illustrates use of the AddNew method to add new rows to a base table. This example assumes that you have read-write access to the table, that the column data provided meets the rules and other constraints associated with the table, and there is a unique index on the table. The data values for the operation are taken from three TextBox controls on the form. Note that the unique key for this table is not provided here as it is provided automatically – it is an identity column.

Option Explicit
Dim er As rdoError
Dim cn As New rdoConnection
Dim qy As New rdoQuery
Dim rs As rdoResultset
Dim col As rdoColumn

Private Sub AddNewJob_Click()
On Error GoTo ANEH

With rs
   .AddNew
   !job_desc = JobDescription
   !min_lvl = MinLevel
   !max_lvl = MaxLevel
   .Update
End With
Exit Sub

UpdateFailed:
MsgBox "Update did not suceed."
rs.CancelUpdate
Exit Sub
A
NEH:
Debug.Print Err, Error
For Each er In rdoErrors
   Debug.Print er
Next
Resume UpdateFailed
    
End Sub

Private Sub Form_Load()

cn.CursorDriver = rdUseOdbc
cn.Connect = "uid=;pwd=;server=sequel;" _
   & "driver={SQL Server};database=pubs;dsn=’’;"
cn.EstablishConnection
With qy
   .Name = "JobsQuery"
   .SQL = "Select * from Jobs"
   .RowsetSize = 1
   Set .ActiveConnection = cn
   Set rs = .OpenResultset(rdOpenKeyset, _
      rdConcurRowver)
   Debug.Print rs.Updatable
End With

Exit Sub
End Sub